﻿﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NcfDB.Model;
using NcfDB.parsing;
using NcfDB.Exception;

namespace NcfDB.Lib
{
    class SQLExecuter
    {
        // 返回结果定义
        private const int SUCC = 1;
        private const int ERR = -1;
        private const int UNKNOWN_FAILURE = 0;
        private string curDBName = null;
        private Table selectResult;
        private string curTableName = null;
        public int SQLExec(List<Word> words) //总入口
        {
            int index;

            selectResult = null;
            curTableName = null;
            index = 0;
            switch (words[index++].type)
            {
                case WORD_TYPE_ENUM.CREATE:
                    if (words[index].type == WORD_TYPE_ENUM.TABLE)//创建新表
                    {
                        string tableName;
                        Dictionary<string,FieldProperty> fields = new Dictionary<string,FieldProperty>();
                        bool haveKey = false;

                        index++;
                        //获取目标表名
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            tableName = words[index].name;
                            index++;
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:CREAT TABLE 缺少标识符");
                            //return ERR;
                        }
                        //语法检查'('
                        if (words[index++].type != WORD_TYPE_ENUM.LEFT_PARENTHESIS) { 
                            throw new SQLParsingException("语法错误:缺少左括号");
                        }
                        //获取参数
                        while (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {

                            FieldProperty newField = new FieldProperty();
                            newField.Name = words[index].name;
                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.FIELD_TYPE)
                            {
                                newField.Type = Utility.fieldTypeParse(words[index].name);
                                if (words[index].name != "BOOL") newField.Length = Int32.Parse(words[index].value);//待测试
                                index++;
                                if(words[index].type == WORD_TYPE_ENUM.KEY_FLAG)
                                {
                                    if(words[index].name == "KEY")
                                    {
                                        if(haveKey)
                                        {
                                            throw new SQLParsingException("语义错误:只能包含一个主键");
                                            //return ERR;
                                        }
                                        newField.isPrimaryKey = true;
                                        haveKey = true;
                                    }
                                    index++;
                                }
                                if(words[index].type == WORD_TYPE_ENUM.NULL_FLAG)
                                {
                                    newField.acceptNull = false;
                                    index++;
                                }
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:缺少数据类型");
                                //return ERR;
                            }
                            fields.Add(newField.Name,newField);
                            if (words[index].type == WORD_TYPE_ENUM.COMMA) index++; else break;//出现逗号继续循环，否则跳出循环

                        }
                        //语法检查')'
                        if (words[index].type != WORD_TYPE_ENUM.RIGHT_PARENTHESIS) { throw new SQLParsingException("语法错误:列名部分需以右括号结尾"); }
                        int flag = CreateTable(tableName, fields);
                        return flag;
                    }
                    else if (words[index++].type == WORD_TYPE_ENUM.DATABASE)
                    {
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            int flag = CreateDB(words[index].name);
                            return flag;
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:CREATE DATABASE 缺少标识符");
                            //return ERR;
                        }
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:CREATE未指明");
                        //return ERR;
                    }
                case WORD_TYPE_ENUM.INSERT:
                    if (words[index++].type == WORD_TYPE_ENUM.INTO)
                    {
                        string tableName;
                        List<string> fieldsName = new List<string>();
                        List<string> values = new List<string>();
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            tableName = words[index].name;
                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.LEFT_PARENTHESIS)
                            {
                                index++;
                                while (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                                {
                                    fieldsName.Add(words[index].name);
                                    index++;
                                    if (words[index].type == WORD_TYPE_ENUM.COMMA) index++;//忽略逗号
                                }
                                if (words[index].type == WORD_TYPE_ENUM.RIGHT_PARENTHESIS)
                                {
                                    index++;
                                }
                                else
                                {
                                    throw new SQLParsingException("语法错误:缺少右括号");
                                    //return ERR;
                                }
                            }
                            if (words[index].type == WORD_TYPE_ENUM.VALUES)
                            {
                                index++;
                                if (words[index].type == WORD_TYPE_ENUM.LEFT_PARENTHESIS)
                                {
                                    index++;
                                    while (words[index].type == WORD_TYPE_ENUM.VALUE)
                                    {
                                        values.Add(words[index].value);
                                        index++;
                                        if (words[index].type == WORD_TYPE_ENUM.COMMA) index++;//忽略逗号
                                    }
                                    if (words[index].type == WORD_TYPE_ENUM.RIGHT_PARENTHESIS)
                                    {
                                        int flag = Insert(tableName, fieldsName, values);
                                        return flag;
                                    }
                                    else
                                    {
                                        throw new SQLParsingException("语法错误:缺少右括号");
                                        //return ERR;
                                    }
                                }
                                else
                                {
                                    throw new SQLParsingException("语法错误:缺少值");
                                    //return ERR;
                                }
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:缺少VALUES");
                                //return ERR;
                            }
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少表名");
                            //return ERR;
                        }
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:缺少INTO");
                        //return ERR;
                    }
                case WORD_TYPE_ENUM.DELETE:
                    if (words[index].type == WORD_TYPE_ENUM.STAR) index++;
                    if (words[index++].type == WORD_TYPE_ENUM.FROM)
                    {
                        string tableName;
                        //todo:删除
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            tableName = words[index++].name;
                            if (words[index].type == WORD_TYPE_ENUM.WHERE) //条件判断
                            {
                                index++;
                            }
                            return Delete(tableName,words,index);
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少标识符");
                            //return ERR;
                        }
                        return UNKNOWN_FAILURE;
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:缺少FROM");
                        //return ERR;
                    }
                case WORD_TYPE_ENUM.EDIT:
                    if (words[index].type == WORD_TYPE_ENUM.TABLE)
                    {
                        string tableName;
                        Database curDB;
                        Dictionary<string,FieldProperty> data = new Dictionary<string,FieldProperty>();
                        FieldProperty newFP;
                        string fieldName;
                        int keyCount = 0;
                        string keyFieldName = null;

                        curDB = CoreDBSystem.getDB(curDBName);

                            index++;
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            tableName = words[index].name;

                            //遍历表，寻找是否有主键
                            for (int i = 0; i < curDB.getTable(tableName).fieldOrder.Count; i++)
                            {
                                if (curDB.getTable(tableName).getFieldProperty(curDB.getTable(tableName).fieldOrder[i]).isPrimaryKey)
                                {
                                    keyCount++;
                                    keyFieldName = curDB.getTable(tableName).fieldOrder[i];
                                    break;
                                }
                            }

                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.LEFT_PARENTHESIS)
                            {
                                do
                                {
                                    index++;
                                    newFP = new FieldProperty();
                                    if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                                    {
                                        fieldName = words[index].name;
                                        newFP.Name = fieldName;
                                        index++;
                                        if (words[index].type == WORD_TYPE_ENUM.FIELD_TYPE)
                                        {
                                            newFP.Type = Utility.fieldTypeParse(words[index].name);
                                            if (words[index].name != "BOOL") newFP.Length = Int32.Parse(words[index].value);//待测试
                                            index++;
                                        }
                                        if (words[index].type == WORD_TYPE_ENUM.KEY_FLAG)
                                        {
                                            if (words[index].name == "KEY")
                                            {
                                                newFP.isPrimaryKey = true;
                                                keyCount++;
                                            }
                                            index++;
                                        }
                                        else
                                        {
                                            if (keyFieldName == fieldName)
                                            {
                                                newFP.isPrimaryKey = false;
                                                keyCount--;
                                            }
                                        }
                                        if (words[index].type == WORD_TYPE_ENUM.NULL_FLAG)
                                        {
                                            newFP.acceptNull = false;
                                            index++;
                                        }
                                        data.Add(newFP.Name, newFP);
                                    }
                                    else
                                    {
                                        throw new SQLParsingException("语法错误:缺少字段名");
                                    }
                                } while (words[index].type == WORD_TYPE_ENUM.COMMA);
                                if (words[index].type == WORD_TYPE_ENUM.RIGHT_PARENTHESIS)
                                {
                                    if (keyCount > 1) throw new SQLParsingException("表中至多只能有一个主键");
                                    int flag = EditTable(tableName,data);
                                    return flag;
                                }
                                else
                                {
                                    throw new SQLParsingException("语法错误:缺少右括号");
                                }
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:缺少左括号");
                            }
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少表名");
                        }
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:缺少TABLE");
                    }
                case WORD_TYPE_ENUM.DROP:
                    if (words[index].type == WORD_TYPE_ENUM.TABLE)
                    {
                        index++;
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            int flag = DropTable(words[index].name);
                            return flag;
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少表名");
                        }
                    }
                    else if (words[index].type == WORD_TYPE_ENUM.DATABASE)
                    {
                        index++;
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            int flag = DropDB(words[index].name);
                            return flag;
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少数据库名");
                        }
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:需指定DROP类型");
                    }
                case WORD_TYPE_ENUM.RENAME:
                    if (words[index].type == WORD_TYPE_ENUM.TABLE)
                    {
                        string oldName;
                        string newName;
                        index++;
                        if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                        {
                            oldName = words[index].name;
                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                            {
                                newName = words[index].name;
                                return RenameTable(oldName, newName);
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:缺少新表名");
                            }
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少目标表名");
                        }
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:缺少TABLE关键字");
                    }
                case WORD_TYPE_ENUM.UPDATE:
                    if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                    {
                        string tableName;
                        Dictionary<string, object> newData = new Dictionary<string,object>();
                        string dataName;
                        object dataValue;
                        Table table;

                        tableName = words[index].name;
                        table = CoreDBSystem.getDB(curDBName).getTable(tableName);
                        index++;
                        //if (words[index].type == WORD_TYPE_ENUM.LEFT_PARENTHESIS)
                        //{
                        //    index++;
                            if (words[index].type == WORD_TYPE_ENUM.SET)
                            {
                                do
                                {
                                    index++;
                                    if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                                    {
                                        dataName = words[index].name;
                                        index++;
                                        if (words[index].type == WORD_TYPE_ENUM.EQL)
                                        {
                                            index++;
                                        }
                                        else
                                        {
                                            throw new SQLParsingException("语法错误:缺少等号");
                                        }
                                        if (words[index].type == WORD_TYPE_ENUM.VALUE)
                                        {
                                            dataValue = words[index].value;
                                            //类型检查
                                            FieldType type;
                                            try { type = table.getFieldProperty(dataName).Type; }catch(System.Exception){ throw new SQLParsingException("无效字段名"); }
                                            if (!Utility.fieldTypeCheck((string)dataValue, type))
                                            {
                                                throw new SQLParsingException("语义错误:数据类型不匹配");
                                            }
                                            index++;
                                            newData.Add(dataName, dataValue);
                                        }
                                        else
                                        {
                                            throw new SQLParsingException("语法错误:缺少值");
                                        }
                                    }
                                    else
                                    {
                                        throw new SQLParsingException("语法错误:缺少字段名");
                                    }
                                } while (words[index].type == WORD_TYPE_ENUM.COMMA);
                                if (words[index].type == WORD_TYPE_ENUM.WHERE)
                                {
                                    index++;
                                }
                                return Update(tableName, newData, words, index);
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:缺少SET");
                            }
                        //}
                        //else
                        //{
                        //    throw new SQLParsingException("语法错误:缺少左括号");
                        //}
                    }
                    else
                    {
                        throw new SQLParsingException("语法错误:需指定表名");
                    }
                case WORD_TYPE_ENUM.SELECT:
                    if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER || words[index].type == WORD_TYPE_ENUM.STAR)
                    {
                        List<string> tarField = new List<string>();
                        string tableName;

                        index--;
                        do
                        {
                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                            {
                                tarField.Add(words[index].name);
                                index++;
                            }
                            else if (words[index].type == WORD_TYPE_ENUM.STAR)
                            {
                                tarField.Add("*");
                                index++;
                                break;
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:需指定字段名");
                            }
                        } while (words[index].type == WORD_TYPE_ENUM.COMMA);
                        if (words[index].type == WORD_TYPE_ENUM.FROM)
                        {
                            index++;
                            if (words[index].type == WORD_TYPE_ENUM.IDENTIFIER)
                            {
                                tableName = words[index].name;
                                index++;
                                if (index < words.Count)
                                {
                                    if (words[index].type == WORD_TYPE_ENUM.WHERE)
                                    {
                                        index++;
                                    }
                                }
                                return Select(tableName, tarField, words, index);
                            }
                            else
                            {
                                throw new SQLParsingException("语法错误:需指定表名");
                            }
                        }
                        else
                        {
                            throw new SQLParsingException("语法错误:缺少FROM");
                        }
                    }
                    break;
                default:
                    break;
            }
            return UNKNOWN_FAILURE;
        }
        private int CreateTable(string tableName, Dictionary<string,FieldProperty> fields)
        {
            curTableName = tableName;
            if (CoreDBSystem.createTable(tableName, fields, curDBName)) return SUCC;
            
            return ERR;
        }
        private int CreateDB(string DBName)
        {
            if (CoreDBSystem.createDB(DBName)) return SUCC;
            return ERR;
        }
        private int Insert(string tableName, List<string> fields, List<string> values)
        {
            Dictionary<string, object> data = new Dictionary<string, object>();
            int columnSize = 0;
            Database curDB;
            Table table;
            //FieldProperty curField;

            curTableName = tableName;
            curDB = CoreDBSystem.getDB(curDBName);
            table = curDB.getTable(tableName);
            if (fields.Count == 0) //若无列名，读取所有列名至fields
            {
                /*  todo:
                 * 获取列数GetSize
                 * 循环体:
                 *      获取列名GetName
                 *      传入fields
                 */
                columnSize = table.fieldOrder.Count;
                for (int i = 0; i < columnSize; i++)
                {
                    fields.Add(table.fieldOrder[i]);
                }

            }
            if (fields.Count != values.Count) { throw new SQLParsingException("语义错误:列名与数据个数不匹配"); }
            for (int i = 0; i < fields.Count; i++)
            {
                FieldType type;
                type = table.getFieldProperty(fields[i]).Type;
                if(Utility.fieldTypeCheck(values[i], type))
                    data.Add(fields[i], (object)values[i]);
                else
                {
                    throw new SQLParsingException("语义错误:数据类型不匹配");
                    //return ERR;
                }
            }

            if (CoreDBSystem.insertRow(tableName, data, curDBName)) return SUCC;
            return ERR;
        }
        private int Delete(string tableName,List<Word> words,int startIndex)
        {
            Database curDB;
            Table table;
            List<List<Word>> whereQueryCollection;
            bool whereReuslt;
            int deleteCount = 0; //删除行数计数器
            int findCount = 0;   //需删除行数计数器
            //List<Word> newWhereQuery;
            //Word newWord;

            curDB = CoreDBSystem.getDB(curDBName);
            table = curDB.getTable(tableName);
            curTableName = tableName;

            whereQueryCollection = Utility.whereConditionParse(table, words, startIndex);
            //初始化where条件查询表
            //for (int i = 0; i < table.Rows.Count; i++)
            //{
            //    newWhereQuery = new List<Word>();
            //    whereQueryCollection.Add(newWhereQuery);
            //}

            //for (int i = startIndex; i < words.Count; i++)
            //{
            //    FieldProperty curFP = table.getFieldProperty(words[i].name); //获取字段
            //    for (int j = 0; j < table.Rows.Count; j++)
            //    {
            //        newWord = new Word();
            //        if (words[i].type == WORD_TYPE_ENUM.IDENTIFIER)//若为标识符，替换为具体数据
            //        {
            //            newWord.type = WORD_TYPE_ENUM.VALUE;
            //            switch (curFP.Type)
            //            {
            //                case FieldType.Double:
            //                case FieldType.Int:
            //                case FieldType.Bool:
            //                    newWord.name = (string)table.Rows[j][words[i].name];
            //                    newWord.value = (string)table.Rows[j][words[i].name];
            //                    break;
            //                case FieldType.Char:
            //                default:
            //                    newWord.name = "'" + table.Rows[j][words[i].name] + "'";
            //                    newWord.value = (string)table.Rows[j][words[i].name];
            //                    break;
            //            }
            //        }
            //        else
            //        {
            //            newWord = words[i];
            //        }
            //        whereQueryCollection[j][i] = newWord;
            //    }
            //}

            for(int i = 0;i<whereQueryCollection.Count;i++)
            {
                try
                {
                    whereReuslt = calculate.calWhere(whereQueryCollection[i]);
                }
                catch (System.Exception)
                {
                    throw new SQLParsingException("WHERE语句不合法");
                }
                if (whereReuslt)
                {
                    findCount++;
                    if (CoreDBSystem.deleteRow(tableName, i, curDBName)) deleteCount++;
                }
            }
            //"需删除"+findCount+"行，已删除"+deleteCount+"行"
            if (deleteCount == findCount) return SUCC;
            return ERR;
        }
        private int EditTable(string tableName, Dictionary<string, FieldProperty> data)
        {
            if (CoreDBSystem.editTable(tableName, data, curDBName)) return SUCC;
            return ERR;
        }
        private int DropTable(string tableName)
        {
            if (CoreDBSystem.dropTable(tableName, curDBName)) return SUCC;
            return ERR;
        }
        private int DropDB(string DBName)
        {
            if (CoreDBSystem.dropDB(DBName)) return SUCC;
            return ERR;
        }
        private int RenameTable(string oldName, string newName)
        {
            if (CoreDBSystem.renameTable(oldName, newName, curDBName)) return SUCC;
            return ERR;
        }
        private int Update(string tableName, Dictionary<string, object> data, List<Word> words, int startIndex)
        {
            Database curDB;
            Table table;
            List<List<Word>> whereQueryCollection;
            bool whereReuslt;
            int updateCount = 0; //更新行数计数器
            int findCount = 0;   //需更新行数计数器

            curDB = CoreDBSystem.getDB(curDBName);
            table = curDB.getTable(tableName);
            curTableName = tableName;

            whereQueryCollection = Utility.whereConditionParse(table, words, startIndex);

            for (int i = 0; i < whereQueryCollection.Count; i++)
            {
                try
                {
                    whereReuslt = calculate.calWhere(whereQueryCollection[i]);
                }
                catch (System.Exception)
                {
                    
                    throw new SQLParsingException("WHERE语句不合法");
                }
                if (whereReuslt)
                {
                    findCount++;
                    if (CoreDBSystem.updateRow(tableName,i,data,curDBName)) updateCount++;
                }

            }
            //"需删除"+findCount+"行，已删除"+updateCount+"行"
            if (updateCount == findCount) return SUCC;
            return ERR;
        }
        private int Select(string tableName, List<string> tarField,List<Word> words,int startIndex)
        {
            Database curDB;
            Table table;
            List<List<Word>> whereQueryCollection;
            bool whereReuslt;
            Dictionary<string, object> rowData;
            Row newRow;
            //todo:逐行读取table
            //判断是否满足条件
            curDB = CoreDBSystem.getDB(curDBName);
            table = curDB.getTable(tableName);

            whereQueryCollection = Utility.whereConditionParse(table, words, startIndex);

            selectResult = new Table();
            if (tarField[0] == "*")
            {
                tarField.Clear();
                foreach (string field in table.fieldOrder)
                {
                    tarField.Add(table.getFieldProperty(field).Name);
                    selectResult.addFieldProperty(table.getFieldProperty(field));
                }
            }
            else
            {
                foreach (string field in tarField)
                {
                    if (table.fieldOrder.Contains(field))
                    {
                        selectResult.addFieldProperty(table.getFieldProperty(field));
                    }
                    else
                    {
                        throw new SQLParsingException("语义错误:无效字段名");
                    }
                }
            }


            for (int i = 0; i < whereQueryCollection.Count; i++)
            {
                //try
                //{
                    whereReuslt = calculate.calWhere(whereQueryCollection[i]);
                //}
                //catch (System.Exception)
                //{

                //    throw new SQLParsingException("WHERE语句不合法");
                //}
                if (whereReuslt)
                {
                    rowData = new Dictionary<string, object>();
                    foreach (string field in tarField)
                    {
                        rowData.Add(field, table.Rows[i][field]);
                    }
                    newRow = new Row();
                    newRow.setData(rowData);
                    selectResult.Rows.Add(newRow);
                }
            }

            return SUCC;

        }

        public Table getSelectResult()
        {
            return selectResult;
        }
        public bool setCurDB(string name)
        {
            curDBName = name;

            return true;
        }

        public string getCurTableName()
        {
            return curTableName;
        }
    }
}
